package com.ivanceras.server.db;


import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Vector;

import com.ivanceras.shared.db.DAO;
import com.ivanceras.shared.db.DB_Rdbms;
import com.ivanceras.shared.db.IDatabase;
import com.ivanceras.shared.db.Filter;
import com.ivanceras.shared.db.IModelDef;
import com.ivanceras.shared.db.Join;
import com.ivanceras.shared.db.ModelDef;
import com.ivanceras.shared.db.Order;
import com.ivanceras.shared.exception.DataTypeException;
import com.ivanceras.shared.exception.DatabaseException;

public abstract class DB_Jdbc extends DB_Rdbms implements IDatabase{

	protected Connection connection = null;
	protected String dbSchema;
	protected String dbName;
	protected boolean initialized = false;
	private boolean isTransacted = false;


	@Override
	public boolean createModel(IModelDef model) throws DatabaseException{
		String sql = buildCreateTableStatement(model);
		return executeUpdateSQL(sql, null) > 0;
	}


	/**
	 * Different implementation accross database
	 * @return
	 */
	protected abstract String getAutoIncrementColumnConstraint();

	@Override
	public int delete(IModelDef model, Filter[] filters) throws DatabaseException {
		String sql = buildDeleteStatement(model, filters);
		return executeUpdateSQL(sql, getFilterValues(filters));
	}

	@Override
	public boolean drop(IModelDef model) throws DatabaseException {
		String sql = buildDropStatement(model);
		return executeUpdateSQL(sql, null) >= 0;
	}


	@Override
	public int empty(IModelDef model) throws DatabaseException {
		String sql = buildEmptyTableStatement(model);
		return executeUpdateSQL(sql, null);
	}


	@Override
	public DAO insert(DAO dao, IModelDef model) throws DatabaseException {
		String[] columns = model.getAttributes();
		String autoIncrementColumn = model.getGeneratedAttribute();
		autoIncrementColumn = getDBElementName(model,autoIncrementColumn);
		String sql = buildInsertStatement(model);
		List<Object> parameters = new ArrayList<Object>();
		for(int i = 0; i < columns.length; i++){
			if(autoIncrementColumn != null && autoIncrementColumn.equals(columns[i])){
				;
			}else{
				Object value = convertDaoValueToDB(dao, columns[i]);
				parameters.add(value);//add the values of the dao as first parameters
			}
		}
		Object[] parameterList = new Object[parameters.size()];
		for(int i = 0; i < parameters.size(); i++){
			parameterList[i] = parameters.get(i);
		}
		if(autoIncrementColumn != null){
			Integer ai = executeInsertSQL(sql, parameterList);
			dao.set_Value(model.getGeneratedAttribute(), ai);
			return dao;
		}else{
			executeInsertSQLNoReturn(sql,parameterList);
		}
		return dao;

	}


	@Override
	public DAO[] select(IModelDef model, Join join, Filter[] filters, Integer offset, Integer limit, Boolean doCount, Order[] orders) throws DatabaseException {
		String sql = buildSelectStatement(model, join, filters, offset, limit, doCount, orders);
		return executeSelectAll(model, sql, getFilterValues(filters));
	}


	@Override
	public DAO update(DAO dao, IModelDef model, Filter[] filters) throws DatabaseException {
		String sql = buildUpdateStatement(model, filters);
		String schema = model.getNamespace();
		schema = getDBElementName(model,schema);

		String[] columns = model.getAttributes();
		String autoIncrementColumn = model.getGeneratedAttribute();
		autoIncrementColumn = getDBElementName(model, autoIncrementColumn);

		List<Object> parameters = new ArrayList<Object>();
//		String[] dataTypes = model.getDataTypes();
		for(int i = 0; i < columns.length; i++){
			if(autoIncrementColumn != null && autoIncrementColumn.equals(columns[i])){
				;
			}else{
				Object value = convertDaoValueToDB(dao, columns[i]);
				parameters.add(value);//add the values of the dao as first parameters
			}
		}
		Object[] filterParams = getFilterValues(filters);
		if(filterParams != null && filterParams.length > 0){
			for(int i = 0; i < filterParams.length; i++){
				parameters.add(filterParams[i]);//then add the filter parameters
			}
		}
		Object[] param = new Object[parameters.size()];
		for(int i = 0; i < parameters.size(); i++){
			param[i] = parameters.get(i); //change to array
		}
		int ret = executeUpdateSQL(sql, param);
		if(ret > 0){
			return dao;
		}else{
			return null;
		}
	}


	private DAO[] executeSelectAll(IModelDef model, String sql, Object[] parameters) throws DatabaseException{
		ResultSet rs = executeSelectSQL(sql, parameters);
		try{
			if(rs != null){
				List<Map<String, Object>> rsl = new ArrayList<Map<String, Object>>();
				ResultSetMetaData md = rs.getMetaData();
				String[] columns = model.getAttributes();
				for(int i = 0; i < columns.length; i++){
					columns[i] = getDBElementName(model,columns[i]);
				}
				int columnCount  = md.getColumnCount();
				int record_cnt = 0;
				while(rs.next()){
					HashMap<String, Object> row = new HashMap<String, Object>();
					for(int i = 0; i < columnCount; i++){
						Object record = rs.getObject(i+1);
						row.put(columns[i].replace("\"","").toLowerCase(), record);

					}
					rsl.add(row);
					record_cnt++;
				}
				DAO[] recordObjs = new DAO[rsl.size()];
				for(int i = 0; i < rsl.size(); i++){
					Map<String, Object> rso = rsl.get(i);
					recordObjs[i] = new DAO(model.getModelName());
					for(int j = 0; j < columns.length; j++){
						Object value = rso.get(columns[j].replace("\"", "").toLowerCase());
						recordObjs[i].set_Value(columns[j], value);
					}
				}
				return recordObjs;
			}
		}catch(SQLException e){
			e.printStackTrace();
			throw new DatabaseException();
		}
		return null;
	}

	//	@Override
	public ResultSet executeSelectSQL(String sql, Object[] parameters) throws DatabaseException{
		//		System.out.print("\n"+sql);
		//		System.out.print((parameters!=null?Arrays.asList(parameters)+"\n":null));
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try{
			pstmt = connection.prepareStatement(sql);
			if(parameters != null){
				int cnt = 0;
				for(int i = 0; i < parameters.length; i++){
					cnt++;
					pstmt.setObject(cnt, parameters[i]);
				}
			}
			rs = pstmt.executeQuery();
		}
		catch(SQLException e){
			System.err.print("\n"+sql);
			System.err.print((parameters!=null?Arrays.asList(parameters):null+"\n"));
			throw new DatabaseException(e.getMessage());
		}
		return rs;
	}

	public int executeUpdateSQL(String sql, Object[] parameters) throws DatabaseException{
		//		System.out.print("\n"+sql);
		//		System.out.print((parameters!=null?Arrays.asList(parameters)+"\n":null));
		try{
			PreparedStatement pstmt = connection.prepareStatement(sql);
			if(parameters!=null){
				for(int i = 0; i < parameters.length; i++){
					pstmt.setObject(i+1, parameters[i]);
				}
			}
			int ret = pstmt.executeUpdate();
			return ret;
		}catch(SQLException e){
			System.out.print("\n"+sql);
			System.out.print((parameters!=null?Arrays.asList(parameters)+"\n":null));
			throw new DatabaseException(e.getMessage());
		}
	}

	public int executeInsertSQL(String sql, Object[] parameters) throws DatabaseException{
		//		System.out.print("\n"+sql);
		//		System.out.print((parameters!=null?Arrays.asList(parameters)+"\n":null));

		try{
			PreparedStatement pstmt = null;
			pstmt = connection.prepareStatement(sql);
			if(parameters!=null){
				for(int i = 0; i < parameters.length; i++){
					pstmt.setObject(i+1, parameters[i]);
				}
			}
			ResultSet rs = pstmt.executeQuery();
			if(rs.next()){
				int id = rs.getInt(1);
				return id;
			}
			return -1;
		}catch (SQLException e){
			System.out.print("\n"+sql);
			System.out.print((parameters!=null?Arrays.asList(parameters)+"\n":null));
			throw new DatabaseException(e.getMessage());

		}
	}


	private void executeInsertSQLNoReturn(String sql, Object[] parameters) throws DatabaseException {
		//		System.out.println(sql);
		//		System.out.println((parameters!=null?Arrays.asList(parameters):null));
		try{
			PreparedStatement pstmt = null;
			pstmt = connection.prepareStatement(sql);
			if(parameters!=null){
				for(int i = 0; i < parameters.length; i++){
					pstmt.setObject(i+1, parameters[i]);
				}
			}
			pstmt.executeUpdate();
		}
		catch(SQLException e){
			System.out.println(sql);
			System.out.println((parameters!=null?Arrays.asList(parameters):null));
			throw new DatabaseException(e.getMessage());
		}
	}

	protected boolean isDriverAvailable(String driverClass) throws ClassNotFoundException
	{
		Class.forName(driverClass);
		return true;
	}
	protected Connection getDBConnection(String dbUrl, String dbUser, String dbPassword)
	{
		Connection c = null;		  
		try {
			c = DriverManager.getConnection(dbUrl, dbUser, dbPassword);	
		} catch (SQLException se) 
		{
			System.err.println("Unable to connect to database "+dbUrl);
			se.printStackTrace();
		}
		return c;
	}

	@Override
	public boolean isClosed()
	{
		try
		{
			if(connection != null){
				return connection.isClosed();
			}else{
				System.err.println("Unable to create database connection");
				//				throw new DatabaseException("Unable to create database connection");
				return true;
			}
		}
		catch(SQLException sx)
		{
			sx.printStackTrace(); // Something is wrong with the connection, let's report it as closed
			try { 
				if(connection != null){connection.close();} 
			} catch(SQLException sx2) { sx2.printStackTrace(); }
			return true;
		}
	}
	@Override
	public boolean reset() 
	{
		boolean rolledback=false;
		try
		{
			if(connection.getAutoCommit()==false) 
			{
				connection.rollback();
				connection.setAutoCommit(true);
				rolledback=true;
			}
		}
		catch(SQLException sx) { sx.printStackTrace(); }
		return rolledback;
	}


	private String[] getPrimaryKeysList(String tableName) throws DatabaseException
	{
		try{
			DatabaseMetaData dmd = connection.getMetaData();
			ResultSet rs = dmd.getPrimaryKeys(dbName, dbSchema, tableName);
			List<String> columnList = new ArrayList<String>();
			try	{
				while(rs.next()) {
					columnList.add(rs.getString("COLUMN_NAME")); } 
			} 
			finally { rs.close(); }
			String[] primaryKeys = columnList.toArray(new String[columnList.size()]);
			return primaryKeys;
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new DatabaseException();
		}
	}

	private class ForeignKey{
		public String[] tableName;
		public String[] columnName;
	}

	@Override
	public IModelDef getModelMetaData(String tableName) throws DatabaseException{

		String realTableName = getRealTableName(tableName);
		boolean caseSensitive = true;
		if(realTableName != null){
			caseSensitive = isCaseSensitive(tableName, realTableName);
		}else{
			System.err.println("No equivalent table in the database: ["+tableName+"]");
			return null;
		}
		Columns columns = getColumnDetails(realTableName);
		String[] primaryKeys = getPrimaryKeysList(realTableName);

		String autoIncrementColumn = getDefaultAutoIncrementColumn(columns);

		ForeignKey fki = getImportedKeys(realTableName);
		ForeignKey fke = getExportedKeys(realTableName);

		IModelDef modelDef = new ModelDef();
		modelDef.setModelName(tableName);
		modelDef.setCaseSensitive(caseSensitive);
		modelDef.setAttributes(columns.getColumns());
		modelDef.setDataTypes(columns.getDataTypes());
		modelDef.setGeneratedAttribute(autoIncrementColumn);
		modelDef.setPrimaryAttributes(primaryKeys);
		modelDef.setHasOne(fki.tableName);
		modelDef.setHasOneForeignColumn(fki.columnName);
		modelDef.setHasMany(fke.tableName);
		modelDef.setHasManyForeignColumn(fke.columnName);
		return modelDef;
	}

	private ForeignKey getExportedKeys(String tablename) throws DatabaseException
	{
		try{
			DatabaseMetaData dmd = connection.getMetaData();
			ResultSet rs = dmd.getExportedKeys(connection.getCatalog(), dbSchema, tablename);
			Vector<String> tableList = new Vector<String>();
			Vector<String> foreignColumn = new Vector<String>();
			while (rs.next()) {
				String fkTableName = rs.getString("FKTABLE_NAME");
				String fkColumnName = rs.getString("FKCOLUMN_NAME");

				if(!tableList.contains(fkTableName)){
					foreignColumn.add(fkColumnName);
					tableList.add(fkTableName);
				}

			}
			String[] ftables = tableList.toArray(new String[tableList.size()]);
			String[] fcolumns = foreignColumn.toArray(new String[foreignColumn.size()]);

			ForeignKey fk = new ForeignKey();
			fk.tableName = ftables;
			fk.columnName = fcolumns;
			return fk;
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new DatabaseException();
		}
	}

	private ForeignKey getImportedKeys(String tablename) throws DatabaseException
	{
		try{
			DatabaseMetaData dmd = connection.getMetaData();
			ResultSet rs = dmd.getImportedKeys(connection.getCatalog(), null, tablename);
			List<String> tableList = new ArrayList<String>();
			List<String> foreignColumn = new ArrayList<String>();
			while (rs.next()) {
				String pkTableName = rs.getString("PKTABLE_NAME");
				String fkColumnName = rs.getString("FKCOLUMN_NAME");
				if(!foreignColumn.contains(fkColumnName)){
					foreignColumn.add(fkColumnName);
					tableList.add(pkTableName);
				}
			}		
			String[] ftables = tableList.toArray(new String[tableList.size()]);
			String[] fcolumns = foreignColumn.toArray(new String[foreignColumn.size()]);

			ForeignKey fk = new ForeignKey();
			fk.tableName = ftables;
			fk.columnName = fcolumns;
			return fk;
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new DatabaseException();
		}
	}

	protected abstract String getRealTableName(String tableName) throws DatabaseException;

	public class Columns{
		private String[] columns = null;
		private String[] dataTypes = null;

		public void setDataTypes(String[] dataTypes) {
			this.dataTypes = dataTypes;
		}
		public String[] getDataTypes() {
			return dataTypes;
		}
		public void setColumns(String[] columns) {
			this.columns = columns;
		}
		public String[] getColumns() {
			return columns;
		}
	}


	protected abstract Columns getColumnDetails(String tableName) throws DatabaseException;


	private String getDefaultAutoIncrementColumn(Columns columns){
		String[] s_columns = columns.getColumns();
		String[] s_dataTypes = columns.getDataTypes();
		for(int i = 0; i < columns.getColumns().length; i++){
			String c = s_columns[i];
			String dt = s_dataTypes[i];
			if(c != null && c.equalsIgnoreCase("id") && (dt.equals("java.lang.Integer") || dt.equals("java.lang.Long"))){
				return c;
			}
		}
		return null;
	}



	public void beginTransaction() throws DatabaseException 
	{
		try{
			if(connection.getAutoCommit()==true)
			{
				connection.setAutoCommit(false);
				isTransacted  = false;
			}
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new DatabaseException();
		}
	}

	public void commitTransaction() throws DatabaseException {
		try{
			connection.commit();
			connection.setAutoCommit(true);
			isTransacted = true;
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new DatabaseException();
		}
	}

	public void rollbackTransaction() throws DatabaseException {
		try{
			if(connection.getAutoCommit()==true); // autocommit
			connection.rollback();
			connection.setAutoCommit(true);
			isTransacted = true;
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new DatabaseException();
		}
	}

	public boolean isTransacted() throws DatabaseException {
		return isTransacted;
	}

	@Override
	public Integer getTotalRecords(String schema, String modelName) throws DatabaseException {
		try{
			String sql = "SELECT count(*) FROM "+schema+"."+modelName;
			ResultSet rs = executeSelectSQL(sql, null);
			//		System.out.println(sql);
			if(rs.next()){
				return rs.getInt(1);
			}else{
				return null;
			}
		}
		catch(SQLException e){
			e.printStackTrace();
			throw new DatabaseException();
		}
	}

	@Override
	public abstract String[] getTableNames(String schema, String tablePattern)
	throws DatabaseException;

	@Override
	public String getEquivalentDBDataType(String dataType) throws DataTypeException{
		if(dataType.equals("java.lang.String")){
			return "character varying";
		}
		else if(dataType.equals("java.lang.Double")){
			return "double precision";
		}
		else if(dataType.equals("java.math.BigDecimal")){
			return "numeric";
		}
		else if(dataType.equals("java.lang.Float")){
			return "real";
		}
		else if(dataType.equals("java.lang.Integer")){
			return "integer";
		}
		else if(dataType.equals("java.lang.Long")){
			return "bigint";
		}
		else if(dataType.equals("java.lang.Boolean")){
			return "boolean";
		}
		else if(dataType.equals("java.lang.Byte[]")){
			return "bytea";
		}
		else if(dataType.equals("java.util.Date")){
			return "date";
		}
		else{
			throw new DataTypeException("No Equivalent Database Data type found for ["+dataType+"]");
		}
	}

	public String getEquivalentJavaDataType(String dataType) throws DataTypeException{
		if(dataType.startsWith("character varying")){//include limitized datatype such as character varying(255)
			return "java.lang.String";
		}
		else if(dataType.startsWith("numeric")){
			return "java.math.BigDecimal";
		}
		else if(dataType.startsWith("integer")){
			return "java.lang.Integer";
		}
		else if(dataType.startsWith("int")){
			return "java.lang.Integer";
		}
		else if(dataType.startsWith("bigint")){
			return "java.lang.Long";
		}
		else if(dataType.startsWith("character")){
			return "java.lang.String";
		}
		else if(dataType.equals("boolean")){
			return "java.lang.Boolean";
		}
		else if(dataType.equals("date")){
			return "java.util.Date";
		}
		else if(dataType.equals("text")){
			return "java.lang.String";
		}
		else if(dataType.equals("name")){
			return "java.lang.String";
		}
		else if(dataType.startsWith("timestamp")){
			return "java.util.Date";
		}
		else if(dataType.equals("bytea")){
			return "java.lang.Byte[]";
		}
		else if(dataType.equals("uuid")){
			return "java.lang.String";
		}
		else{
			throw new DataTypeException("No Equivalent Java Data type found for ["+dataType+"]");
		}

	}

	@Override
	protected Object convertDaoValueToDB(DAO dao, String column){
		column = column.replace("\"", "");
		if(dao == null) return null;
		Object value = dao.get_Value(column);
		if(value == null){
			return null;
		}
		if(value.getClass().equals(java.util.Date.class)){
			Timestamp time = new Timestamp(((Date)value).getTime());
			return time;
		}
		else{
			return value;
		}
	}

}
